package com.hst.web.sys.quartz;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.hibernate.HibernateException;
import org.hibernate.engine.spi.SharedSessionContractImplementor;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.EnableScheduling;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;

import com.hst.core.MapBean;
import com.hst.core.dao.IDService;
import com.hst.core.dao.IORDao;
import com.hst.core.dao.ISQLDao;
import com.hst.core.dao.ORQuery;
import com.hst.core.dao.ORQuery.Op;
import com.hst.core.spring.ContextHelper;
import com.hst.web.base.entity.TAccountIdentity;
import com.hst.web.base.entity.TAccountInfo;
import com.hst.web.base.entity.TCompanyEmployee;
import com.hst.web.base.entity.TDeptEmployee;
import com.hst.web.base.entity.TEmployeeOperate;
import com.hst.web.base.entity.TReivewHistory;
import com.hst.web.base.entity.TVehicleInfo;
import com.hst.web.base.entity.TVehicleInfoHistory;
import com.hst.web.base.ui.FlowHistory;
import com.hst.web.sys.entity.TUser;
import com.hst.web.sys.web.DataMigrationController;

/**
 * @Description: 数据库同步定时任务
 * @Author: duyong
 * @Date: 
 */
@Component
@EnableScheduling   // 开启定时任务
public class DBSyncQuartz {
	Logger logger = LoggerFactory.getLogger(DBSyncQuartz.class);
	
	@Autowired
	IORDao dao;

	@Autowired
	ISQLDao sql;
	
	@Autowired
	IDService idService;
	
	@Autowired
	DataMigrationController dataMigration;
	
	//数据同步数据,本系统应用到的数据表数据同步
	//添加定时任务
	//@Scheduled(cron = "0 0 0,12 * * ?")		//每天0、12点触发一次
	//@Scheduled(cron = "0 0/10 * * * ?")		//每隔10分钟触发一次
	public void DBSyncQuartz() {
		//本系统应用到的数据表数据同步
		//dbSync();

		//同步本系统不用的数据表,但其他系统需要使用的数据表
		//dbDirectSync();
		
		//反向同步数据(新库到旧库)
		//dbReverseDirectSync();
		
		//人员照片取得
		//dataMigration.readAccountIdentity();
		//车辆照片取得
		//dataMigration.readVehicleInfo();
		//读取车辆审批表中的照片
		//dataMigration.readAskpassVehicleInfo();
		//企业照片取得
		//dataMigration.readCompanyInfo();
		//承包商照片取得
		//dataMigration.readContractorInfo();
	}
	
	//数据同步数据,同步本系统不用的数据表,但其他系统需要使用的数据表
	//@Scheduled(cron = "0 0 3,6,9,15,18,21 * * ?")		//每天3,6,9,15,18,21点触发一次
	public void DBDirectSyncQuartz() {
		//同步表结构基本一致的表,直接根据主键同步
		//dbDirectSync();
		
		//反向同步数据(新库到旧库)
		//dbReverseDirectSync();
	}
	
	//同步表结构基本一致的表,直接根据主键同步
	public void dbDirectSync() {
		List<ORQuery> querys = new ArrayList<ORQuery>();
		Map<String, Object> params = new HashMap<>();
		List<MapBean> listData = new ArrayList<MapBean>();
		
		//t_control_data布控人员车辆表同步,全部删除 
		logger.debug("Sync truncate-control-data Information begin.");
		sql.delete("dbsync.truncate-control-data", params);
		logger.debug("Sync truncate-control-data Information finished.");
		
		//t_control_data布控人员车辆表同步,全部新增
		logger.debug("Sync insert-control-data Information begin.");
		sql.create("dbsync.insert-control-data", params);
		logger.debug("Sync insert-control-data Information finished.");
		
		//t_askpass_detail_pers通行证申请详情(人员)表,导入T_ASKPASS_DETAIL_PERS表数据 
//		logger.debug("Sync insert-askpass-detail-pers begin.");
//		sql.create("dbsync.insert-askpass-detail-pers", params);
//		logger.debug("Sync insert-askpass-detail-pers Information finished.");
		
		//t_askpass_detail_veh通行证申请详情(车辆)表,导入T_ASKPASS_DETAIL_VEH表数据
//		logger.debug("Sync insert-askpass-detail-veh begin.");
//		sql.create("dbsync.insert-askpass-detail-veh", params);
//		logger.debug("Sync insert-askpass-detail-veh Information finished.");
		
	}
	
	//反向同步数据(新库到旧库)
	public void dbReverseDirectSync() {
		List<ORQuery> querys = new ArrayList<ORQuery>();
		Map<String, Object> params = new HashMap<>();
		List<MapBean> listData = new ArrayList<MapBean>();
		
		//t_manager_info@orcl企业账户数据反写
		logger.debug("Sync insert-manager-data-reverse1 Information begin.");
		sql.delete("dbsync.insert-manager-data-reverse1", params);
		logger.debug("Sync insert-manager-data-reverse1 Information finished.");
		
		//t_manager_info@orcl承包商账户数据反写
		logger.debug("Sync insert-manager-data-reverse2 Information begin.");
		sql.create("dbsync.insert-manager-data-reverse2", params);
		logger.debug("Sync insert-manager-data-reverse2 Information finished.");

		
	}	
	
	
	//同步表结构不一致的表
	public void dbSync() {
		List<ORQuery> querys = new ArrayList<ORQuery>();
		Map<String, Object> params = new HashMap<>();
		List<MapBean> listData = new ArrayList<MapBean>();
		logger.debug("Sync data from old db begin... ");
		
		//t_company_info企业信息表
		logger.debug("Sync insert-company-info begin.");
		sql.create("dbsync.insert-company-info", params);
		logger.debug("Sync insert-company-info Information finished.");
		
		//t_company_dept企业部门信息表
		logger.debug("Sync insert-company-dept Information begin.");
		sql.create("dbsync.insert-company-dept", params);
		logger.debug("Sync insert-company-dept Information finished.");
		
		//t_contractor_info承包商信息表
		logger.debug("Sync insert-contractor-info Information begin.");
		sql.create("dbsync.insert-contractor-info", params);
		logger.debug("Sync insert-contractor-info Information finished.");
		
		//t_company_contractor企业和承包商关联表
		logger.debug("Sync insert-company-contractor Information begin.");
		sql.create("dbsync.insert-company-contractor", params);
		logger.debug("Sync insert-company-contractor Information finished.");
		
		//t_company_contractor企业和承包商关联-补齐承包商别名
		logger.debug("Sync update-company-contractor Information begin.");
		sql.update("dbsync.update-company-contractor", params);
		logger.debug("Sync update-company-contractor Information finished.");
		
		
		//t_company_employee人员信息表(企业员工)
		logger.debug("Sync company_employee Information begin.");
		//在现有系统中不存在的数据并且证件号也不存在的数据
		listData = (List<MapBean>) sql.query("dbsync.list-company-employee", params);
		for (MapBean d : listData){
			String employeeCode = (String) d.get("employee_code");
			String fullName = (String) d.get("fullname");
			String papersNumber = (String) d.get("papers_number");
			String papersType = (String) d.get("papers_type");
			String issueNo = (String) d.get("pass_no");
			String passOld = (String) d.get("pass_old");
			String oldpassStatus = (String) d.get("oldpass_status");
			String companyCode = (String) d.get("company_code");
			String deptCode = (String) d.get("department");
			Timestamp createTime = (Timestamp) d.get("op_time");

			String strKey = papersType + "_" + papersNumber;
			//Map<String, Object> mapCompanyEmployee = new HashMap<>();
			if (papersType != null && !"".equals(papersType) && papersNumber != null && !"".equals(papersNumber)) {
				querys.clear();
				querys.add(new ORQuery(Op.eq, "papersType", papersType));
				querys.add(new ORQuery(Op.eq, "papersNumber", papersNumber));
				List<TCompanyEmployee> listCompanyEmployee= dao.list(TCompanyEmployee.class, querys);
				if (listCompanyEmployee.size() > 0) {	//证件号已经存在的数据 (有重复数据)
					TCompanyEmployee companyEmployee = listCompanyEmployee.get(0);
					employeeCode = companyEmployee.getEmployeeCode();	//有重复数据时,取得保存的唯一一条员工信息代码
					//有重复数据时补充通行证号
					if (companyEmployee.getIssueNo() == null && "".equals(companyEmployee.getIssueNo())) {
						if (issueNo != null && !"".equals(issueNo)) {
							companyEmployee.setIssueNo(issueNo);
						}
					}
					if (companyEmployee.getPassOld() == null && "".equals(companyEmployee.getPassOld())) {
						if (passOld != null && !"".equals(passOld)) {
							companyEmployee.setPassOld(passOld);
						}
					}
					dao.update(companyEmployee);
				} else {
					Map<String, Object> values = new HashMap<>();
					values.put("employeeCode", employeeCode);
					values.put("fullName", fullName);
					values.put("papersNumber", papersNumber);
					values.put("papersType", papersType);
					values.put("issueNo", issueNo);
					values.put("passOld", passOld);
					values.put("oldpassStatus", oldpassStatus);
					values.put("createTime", createTime);
					sql.create("dbsync.insert-company-employee", values);
				}
				
				//更新员工的部门关联信息
				querys.clear();
				querys.add(new ORQuery(Op.eq, "opType", "2"));	//人员离职
				querys.add(new ORQuery(Op.eq, "employeeCode", employeeCode));
				querys.add(new ORQuery(Op.eq, "companyCode", companyCode));
				if (deptCode != null && "".equals(deptCode)) {
					querys.add(new ORQuery(Op.eq, "deptCode", deptCode));
				}
				//查询员工离职信息,如果有离职信息则不再处理关联数据
				List<TEmployeeOperate> listEmployeeOperate= dao.list(TEmployeeOperate.class, querys);
				if (listEmployeeOperate.size() == 0) {		//没有做过离职操作时允许再次同步人员和部门管理关系
					querys.clear();
					querys.add(new ORQuery(Op.eq, "employeeCode", employeeCode));
					querys.add(new ORQuery(Op.eq, "companyCode", companyCode));
					if (deptCode != null && "".equals(deptCode)) {
						querys.add(new ORQuery(Op.eq, "deptCode", deptCode));
					}
					List<TDeptEmployee> listDeptEmployee= dao.list(TDeptEmployee.class, querys);
					if (listDeptEmployee.size() == 0) {
						TDeptEmployee deptEmployee = new TDeptEmployee();
						deptEmployee.setEmployeeCode(employeeCode);
						deptEmployee.setCompanyCode(companyCode);
						deptEmployee.setDeptCode(deptCode);
						deptEmployee.setCreateUserid("superadmin");
						dao.save(deptEmployee);
					}
				}
			}
		}
		logger.debug("Sync company_employee Information finished.");

		//t_company_employee人员信息表(承包商员工)
		logger.debug("Sync contractor_employee Information begin.");
		//在现有系统中不存在的数据并且证件号也不存在的数据
		listData = (List<MapBean>) sql.query("dbsync.list-contractor-employee", params);
		for (MapBean d : listData){
			String employeeCode = (String) d.get("employee_code");
			String fullName = (String) d.get("fullname");
			String papersNumber = (String) d.get("papers_number");
			String papersType = (String) d.get("papers_type");
			String issueNo = (String) d.get("pass_no");
			String passOld = (String) d.get("pass_old");
			String oldpassStatus = (String) d.get("oldpass_status");
			Timestamp createTime = (Timestamp) d.get("op_time");

			String strKey = papersType + "_" + papersNumber;
			//Map<String, Object> mapCompanyEmployee = new HashMap<>();
			if (papersType != null && !"".equals(papersType) && papersNumber != null && !"".equals(papersNumber)) {
				querys.clear();
				querys.add(new ORQuery(Op.eq, "papersType", papersType));
				querys.add(new ORQuery(Op.eq, "papersNumber", papersNumber));
				List<TCompanyEmployee> listCompanyEmployee= dao.list(TCompanyEmployee.class, querys);
				if (listCompanyEmployee.size() > 0) {	//证件号已经存在的数据 (有重复数据)
				} else {
					Map<String, Object> values = new HashMap<>();
					values.put("employeeCode", employeeCode);
					values.put("fullName", fullName);
					values.put("papersNumber", papersNumber);
					values.put("papersType", papersType);
					values.put("issueNo", issueNo);
					values.put("passOld", passOld);
					values.put("oldpassStatus", oldpassStatus);
					values.put("createTime", createTime);
					sql.create("dbsync.insert-company-employee", values);
				}
			}
		}
		logger.debug("Sync contractor_employee Information finished.");
		
		//t_contractor_xfiles原有承包商人员信息表同步(原有表有blob字段关联查询会报错)
		logger.debug("Sync insert-contractor-xfiles Information begin.");
		sql.create("dbsync.insert-contractor-xfiles", params);
		logger.debug("Sync insert-contractor-xfiles Information finished.");
		
		//t_employee_contractordept人员和承包商部门关联表
		logger.debug("Sync insert-employee-contractordept Information begin.");
		sql.create("dbsync.insert-employee-contractordept", params);
		logger.debug("Sync insert-employee-contractordept Information finished.");
		
		//t_company_employee承包商人员信息表,同步通行证号1
	    //同步通行证号,以t_company_employee@orcl表为准(注意先后顺序)
	    //先同步t_contractor_xfiles@orcl表,再t_company_employee@orcl表
//		logger.debug("Sync update-contractor-employee-pass-no-1 Information begin.");
//		sql.update("dbsync.update-contractor-employee-pass-no-1", params);
//		logger.debug("Sync update-contractor-employee-pass-no-1 Information finished.");
		
		//t_company_employee承包商人员信息表,同步旧版通行证号1
//		logger.debug("Sync update-contractor-employee-pass-old-1 Information begin.");
//		sql.update("dbsync.update-contractor-employee-pass-old-1", params);
//		logger.debug("Sync update-contractor-employee-pass-old-1 Information finished.");	
		
		//t_company_employee承包商人员信息表,同步旧版通行证状态
//		logger.debug("Sync update-contractor-employee-oldpass-status-1 Information begin.");
//		sql.update("dbsync.update-contractor-employee-oldpass-status-1", params);
//		logger.debug("Sync update-contractor-employee-oldpass-status-1 Information finished.");
		
		//t_company_employee人员信息表,同步通行证号1
	    //同步通行证号,以t_company_employee@orcl表为准(注意先后顺序)
	    //先同步t_contractor_xfiles@orcl表,再t_company_employee@orcl表
//		logger.debug("Sync update-company-employee-pass-no-1 Information begin.");
//		sql.update("dbsync.update-company-employee-pass-no-1", params);
//		logger.debug("Sync update-company-employee-pass-no-1 Information finished.");
		
		//t_company_employee人员信息表,同步旧版通行证号1
//		logger.debug("Sync update-company-employee-pass-old-1 Information begin.");
//		sql.update("dbsync.update-company-employee-pass-old-1", params);
//		logger.debug("Sync update-company-employee-pass-old-1 Information finished.");	
		
		//t_company_employee人员信息表,同步旧版通行证状态1
//		logger.debug("Sync update-company-employee-oldpass-status-1 Information begin.");
//		sql.update("dbsync.update-company-employee-oldpass-status-1", params);
//		logger.debug("Sync update-company-employee-oldpass-status-1 Information finished.");	
		
		
		//t_account_info账户信息表
		logger.debug("Sync insert-account-info Information begin.");
		sql.create("dbsync.insert-account-info", params);
		logger.debug("Sync insert-account-info Information finished.");
		
		//t_account_identity账户实名身份信息表
		logger.debug("Sync insert-account-identity Information begin.");
		sql.create("dbsync.insert-account-identity", params);
		logger.debug("Sync insert-account-identity Information finished.");
		
		//t_account_identity账户实名身份信息表,根据证件号和证件类型对接实名认证数据中的员工号
		logger.debug("Sync update-account-identity Information begin.");
		sql.update("dbsync.update-account-identity", params);
		logger.debug("Sync update-account-identity Information finished.");
		
		//t_account_identity账户实名身份信息表,更新实名信息中的"地址"字段
		logger.debug("Sync update-account-identity-address Information begin.");
		sql.update("dbsync.update-account-identity-address", params);
		logger.debug("Sync update-account-identity-address Information finished.");
		
		//t_account_identity账户实名身份信息表,更新实名信息中的"职务"字段
		logger.debug("Sync update-account-identity-occupation Information begin.");
		sql.update("dbsync.update-account-identity-occupation", params);
		logger.debug("Sync update-account-identity-occupation Information finished.");
		
		//t_account_identity账户实名身份信息表,更新实名信息中的"手机"字段
		logger.debug("Sync update-account-identity-telnumber Information begin.");
		sql.update("dbsync.update-account-identity-telnumber", params);
		logger.debug("Sync update-account-identity-telnumber Information finished.");
		
		//t_account_identity同步管理员手机号到实名认证表中1
		logger.debug("Sync update-account-identity-telnumber-bymanager1 Information begin.");
		sql.update("dbsync.update-account-identity-telnumber-bymanager1", params);
		logger.debug("Sync update-account-identity-telnumber-bymanager1 Information finished.");
		
		//t_account_identity同步管理员手机号到实名认证表中2
		logger.debug("Sync update-account-identity-telnumber-bymanager2 Information begin.");
		sql.update("dbsync.update-account-identity-telnumber-bymanager2", params);
		logger.debug("Sync update-account-identity-telnumber-bymanager2 Information finished.");
		
		//t_account_identity账户实名身份信息表,同步APP账户密码
		logger.debug("Sync update-account-identity-password Information begin.");
		sql.update("dbsync.update-account-identity-password", params);
		logger.debug("Sync update-account-identity-password Information finished.");
		
		//t_company_employee人员信息表,更新identify_flag字段
		logger.debug("Sync update-company-employee-identifyflag Information begin.");
		sql.update("dbsync.update-company-employee-identifyflag", params);
		logger.debug("Sync update-company-employee-identifyflag Information finished.");
		
		//t_manager_info管理员信息表
		logger.debug("Sync manager-info Information begin.");
		listData = (List<MapBean>) sql.query("dbsync.list-manager-info", params);
		for (MapBean d : listData){
			String operatorNbr = (String) d.get("operator_nbr");
			String password = (String) d.get("password");
			String loginName = (String) d.get("fullname");
			String companyCode= (String) d.get("company_code");
			String telNumber= (String) d.get("tel_number");
			String userType = (String) d.get("user_type");
			String createUserid = (String) d.get("op_create");
			Timestamp createTime = (Timestamp) d.get("op_time");
			String userTypeOld = (String) d.get("user_typeold");
			
			String employeeCode = "";
			//卡口办证保安+卡口检查保安,在原有系统中未输入证件号，另外处理
			if ("5".equals(userTypeOld) || "6".equals(userTypeOld))	{
				//配对企业人员表
				if (StringUtils.isNotEmpty(companyCode) && StringUtils.isNotEmpty(loginName)) {
					params.clear();
					params.put("companyCode", companyCode);
					params.put("fullName", loginName);
					List<MapBean> list = (List<MapBean>) sql.query("dbsync.list-employee-by-companycode-fullname", params);
					if (list.size() > 0) {
						employeeCode = (String) list.get(0).get("employee_code");
					}
					
					//补充数据到TAccountInfo表,由于t_account_identity的证件号是必输的,因此无法补充
					querys.clear();
					querys.add(new ORQuery(Op.eq, "userName", operatorNbr));
					List<TAccountInfo> listAccountInfo = dao.list(TAccountInfo.class, querys);
					if (listAccountInfo.size() == 0) {	//AccountInfo表数据不存在时,补充一条登录数据
						TAccountInfo accountInfo = new TAccountInfo();
						accountInfo.setUserName(operatorNbr);
						accountInfo.setPassword(password);
						accountInfo.setTelNumber(telNumber);
						dao.save(accountInfo);
					}
					
					//补充实名认证表的电话号码
					if (StringUtils.isNotEmpty(employeeCode)) {
						querys.clear();
						querys.add(new ORQuery(Op.eq, "employeeCode", employeeCode));
						List<TAccountIdentity> listAccountIdentity = dao.list(TAccountIdentity.class, querys);
						if (listAccountIdentity.size() > 0) {
							TAccountIdentity accountIdentity= listAccountIdentity.get(0);
							if (StringUtils.isNotEmpty(telNumber) && StringUtils.isEmpty(accountIdentity.getTelNumber())) {
								accountIdentity.setTelNumber(telNumber);
								dao.update(accountIdentity);
							}
						}
					}
				}
			}
			
			TUser user = new TUser();
			user.setOperatorNbr(operatorNbr);
			user.setPassword(password);
			user.setLoginName(loginName);
			user.setUserType(userType);
			user.setCreateUserid(createUserid);
			user.setCreateTime(createTime);
			user.setUserTypeOld(userTypeOld);
			user.setValidflag("1");
			if (StringUtils.isNotEmpty(employeeCode)) {
				user.setEmployeeCode(employeeCode);
			}
			dao.save(user);

		}
		logger.debug("Sync manager-info Information finished.");
		
		//t_manager_info管理员信息表,为管理员用户关联人员信息(从人员认证表中取得)
		logger.debug("Sync update-manager_info-employee-code_1 Information begin.");
		sql.update("dbsync.update-manager_info-employee-code_1", params);
		logger.debug("Sync update-manager_info-employee-code_1 Information finished.");
		
		//t_manager_info管理员信息表,为管理员用户关联人员信息(从原始数据库中关联取得)
		logger.debug("Sync update-manager_info-employee-code_2 Information begin.");
		sql.update("dbsync.update-manager_info-employee-code_2", params);
		logger.debug("Sync update-manager_info-employee-code_2 Information finished.");
		
		//t_manager_info管理员信息表,同步管理员密码
//		logger.debug("Sync update-manager-info-password Information begin.");
//		sql.update("dbsync.update-manager-info-password", params);
//		logger.debug("Sync update-manager-info-password Information finished.");
		
		//t_manager_info同步管理员用户类型(user_typeold),有变更时
//		logger.debug("Sync update-manager-info-user-typeold-1 Information begin.");
//		sql.update("dbsync.update-manager-info-user-typeold-1", params);
//		logger.debug("Sync update-manager-info-user-typeold-1 Information finished.");
		
		//t_manager_info同步管理员用户类型(user_typeold),原有库有删除时
//		logger.debug("Sync update-manager-info-user-typeold-2 Information begin.");
//		sql.update("dbsync.update-manager-info-user-typeold-2", params);
//		logger.debug("Sync update-manager-info-user-typeold-2 Information finished.");

		//sys_user_role用户角色关系表
		logger.debug("Sync insert-user-role Information begin.");
		sql.create("dbsync.insert-user-role", params);
		logger.debug("Sync insert-user-role Information finished.");
		
		//t_dept_managerinfo用户管辖部门
		logger.debug("Sync insert-dept-managerinfo Information begin.");
		sql.create("dbsync.insert-dept-managerinfo", params);
		logger.debug("Sync insert-dept-managerinfo Information finished.");
		
		//t_contractor_managerinfo用户管辖承包商
		logger.debug("Sync insert-contractor-managerinfo Information begin.");
		sql.create("dbsync.insert-contractor-managerinfo", params);
		logger.debug("Sync insert-contractor-managerinfo Information finished.");
		

		//t_vehicle_info车辆信息表(第一部分)导入(t_vehicle_info) 表
		logger.debug("Sync list-vehicle-info-1 Information begin.");
		listData = (List<MapBean>) sql.query("dbsync.list-vehicle-info-1", params);
		for (MapBean d : listData){
			String employeeCode = (String) d.get("employee_code");
			String plateNo = (String) d.get("plate_no");
			String plateColor = (String) d.get("plate_color");
			String engineNo = (String) d.get("engine_no");
			String ownerName = (String) d.get("owner_name");
			String vehicleType = (String) d.get("vehicle_type");
			String useCharacter = (String) d.get("use_character");
			String goods = (String) d.get("goods");
			String vrdTransportNo = (String) d.get("v_rdtransport_no");
			String vrdTransportExp = (String) d.get("v_rd_transport_exp");
			String policyExp = (String) d.get("policy_exp");
			String issueNo = (String) d.get("issue_no");
			String passOld = (String) d.get("pass_old");
			String oldpassStatus = (String) d.get("oldpass_status");
			Timestamp createTime = (Timestamp) d.get("op_time");
			
			//先判断车辆是否被手工删除过
			querys.clear();
			querys.add(new ORQuery(Op.eq, "employeeCode", employeeCode));
			querys.add(new ORQuery(Op.eq, "plateNo", plateNo));
			querys.add(new ORQuery(Op.eq, "engineNo", engineNo));
			
			//查询车辆删除历史信息,如果有删除信息则不再处理关联数据
			List<TVehicleInfoHistory> listVehicleInfoHistory= dao.list(TVehicleInfoHistory.class, querys);
			if (listVehicleInfoHistory.size() == 0) {		//没有做过删除操作时允许再次导入
				TVehicleInfo vehicleInfo = new TVehicleInfo();
				vehicleInfo.setEmployeeCode(employeeCode);
				vehicleInfo.setPlateNo(plateNo);
				vehicleInfo.setPlateColor(plateColor);
				vehicleInfo.setEngineNo(engineNo);
				vehicleInfo.setOwnerName(ownerName);
				vehicleInfo.setVehicleType(vehicleType);
				vehicleInfo.setUseCharacter(useCharacter);
				vehicleInfo.setGoods(goods);
				vehicleInfo.setVrdTransportNo(vrdTransportNo);
				vehicleInfo.setVrdTransportExp(vrdTransportExp);
				vehicleInfo.setPolicyExp(policyExp);
				vehicleInfo.setIssueNo(issueNo);
				vehicleInfo.setPassOld(passOld);
				vehicleInfo.setOldpassStatus(oldpassStatus);

				vehicleInfo.setCreateTime(createTime);
				
				vehicleInfo.setCheckStatus("0");
				vehicleInfo.setReviewStatus("0");

				dao.save(vehicleInfo);
			}
		}
		logger.debug("Sync list-vehicle-info-1 Information finished.");
		
		//t_vehicle_info车辆信息表(第二部分)导入(t_employee_vehicle) 表
		logger.debug("Sync list-vehicle-info-2 Information begin.");
		listData = (List<MapBean>) sql.query("dbsync.list-vehicle-info-2", params);
		for (MapBean d : listData){
			String employeeCode = (String) d.get("employee_code");
			String plateNo = (String) d.get("plate_no");
			String plateColor = (String) d.get("plate_color");
			String issueNo = (String) d.get("issue_no");
			String passOld = (String) d.get("pass_old");
			String oldpassStatus = (String) d.get("oldpass_status");
			Timestamp createTime = (Timestamp) d.get("op_time");
			
			//先判断车辆是否被手工删除过
			querys.clear();
			querys.add(new ORQuery(Op.eq, "employeeCode", employeeCode));
			querys.add(new ORQuery(Op.eq, "plateNo", plateNo));
			
			//查询车辆删除历史信息,如果有删除信息则不再处理关联数据
			List<TVehicleInfoHistory> listVehicleInfoHistory= dao.list(TVehicleInfoHistory.class, querys);
			if (listVehicleInfoHistory.size() == 0) {		//没有做过删除操作时允许再次导入
				TVehicleInfo vehicleInfo = new TVehicleInfo();
				vehicleInfo.setEmployeeCode(employeeCode);
				vehicleInfo.setPlateNo(plateNo);
				vehicleInfo.setPlateColor(plateColor);
				vehicleInfo.setIssueNo(issueNo);
				vehicleInfo.setPassOld(passOld);
				vehicleInfo.setOldpassStatus(oldpassStatus);
				vehicleInfo.setCreateTime(createTime);
				
				vehicleInfo.setCheckStatus("0");
				vehicleInfo.setReviewStatus("0");
				
				dao.save(vehicleInfo);				
			}

		}
		logger.debug("Sync list-vehicle-info-2 Information finished.");	
		
		//t_vehicle_info车辆信息表(第三部分)导入(t_contractor_vehicle) 表 
		logger.debug("Sync list-vehicle-info-3 Information begin.");
		listData = (List<MapBean>) sql.query("dbsync.list-vehicle-info-3", params);
		for (MapBean d : listData){
			String employeeCode = (String) d.get("employee_code");
			String plateNo = (String) d.get("plate_no");
			String plateColor = (String) d.get("plate_color");
			String issueNo = (String) d.get("issue_no");
			String passOld = (String) d.get("pass_old");
			String oldpassStatus = (String) d.get("oldpass_status");
			Timestamp createTime = (Timestamp) d.get("op_time");
			
			//先判断车辆是否被手工删除过
			querys.clear();
			querys.add(new ORQuery(Op.eq, "employeeCode", employeeCode));
			querys.add(new ORQuery(Op.eq, "plateNo", plateNo));
			
			//查询车辆删除历史信息,如果有删除信息则不再处理关联数据
			List<TVehicleInfoHistory> listVehicleInfoHistory= dao.list(TVehicleInfoHistory.class, querys);
			if (listVehicleInfoHistory.size() == 0) {		//没有做过删除操作时允许再次导入
				TVehicleInfo vehicleInfo = new TVehicleInfo();
				vehicleInfo.setEmployeeCode(employeeCode);
				vehicleInfo.setPlateNo(plateNo);
				vehicleInfo.setPlateColor(plateColor);
				vehicleInfo.setIssueNo(issueNo);
				vehicleInfo.setPassOld(passOld);
				vehicleInfo.setOldpassStatus(oldpassStatus);
				vehicleInfo.setCreateTime(createTime);
				
				vehicleInfo.setCheckStatus("0");
				vehicleInfo.setReviewStatus("0");
				
				dao.save(vehicleInfo);
			}
		}
		logger.debug("Sync list-vehicle-info-3 Information finished.");	
		
		
		//t_vehicle_info车辆信息表(第四部分)导入(t_askpass_detail_veh) 表
		logger.debug("Sync list-vehicle-info-4 Information begin.");
		params.clear();
		listData = (List<MapBean>) sql.query("dbsync.list-vehicle-info-4", params);
		for (MapBean d : listData){
			String companyCode = (String) d.get("company_code");
			String passType = (String) d.get("pass_type");		//12-企业车辆通行证,22-承包商车辆通行证(暂时未使用)
			String fullName = (String) d.get("fullname");
			String plateNo = (String) d.get("plate_no");
			String plateColor = (String) d.get("plate_color");
			String engineNo = (String) d.get("engine_no");
			String ownerName = (String) d.get("owner_name");
			String vehicleType = (String) d.get("vehicle_type");
			String useCharacter = (String) d.get("use_character");
			String goods = (String) d.get("goods");
			String vrdTransportNo = (String) d.get("v_rdtransport_no");
			String vrdTransportExp = (String) d.get("v_rdtransport_exp");
			String policyExp = (String) d.get("policy_exp");
			String passno_person = (String) d.get("passno_person");		//未使用
			String fileNo = (String) d.get("file_no");
			String drvlicenceType = (String) d.get("drvlicence_type");
			String certificateType = (String) d.get("certificatetype");
			String prdTransportNo = (String) d.get("p_rdtransport_no");
			String prdTransportExp = (String) d.get("p_rdtransport_exp");			
			String supercargoName = (String) d.get("supercargo");
			String escortcert = (String) d.get("escortcert");			//未使用
			Timestamp createTime = (Timestamp) d.get("op_time");

			if (StringUtils.isNotEmpty(companyCode) && StringUtils.isNotEmpty(fullName)) {
				
				//先当做企业车辆信息进行匹配
				params.clear();
				params.put("companyCode", companyCode);
				params.put("fullName", fullName);
				List<MapBean> list = (List<MapBean>) sql.query("dbsync.list-employee-by-companycode-fullname", params);
				if (list.size() > 0) {
					String employeeCode = (String) list.get(0).get("employee_code");
					//先判断车辆是否被手工删除过
					querys.clear();
					querys.add(new ORQuery(Op.eq, "employeeCode", employeeCode));
					querys.add(new ORQuery(Op.eq, "plateNo", plateNo));
					
					//查询车辆删除历史信息,如果有删除信息则不再处理关联数据
					List<TVehicleInfoHistory> listVehicleInfoHistory= dao.list(TVehicleInfoHistory.class, querys);
					if (listVehicleInfoHistory.size() == 0) {		//没有做过删除操作时允许再次导入
						TVehicleInfo vehicleInfo = new TVehicleInfo();
						vehicleInfo.setEmployeeCode(employeeCode);
						vehicleInfo.setPlateNo(plateNo);
						vehicleInfo.setPlateColor(plateColor);
						vehicleInfo.setEngineNo(engineNo);
						vehicleInfo.setOwnerName(ownerName);
						vehicleInfo.setVehicleType(vehicleType);
						vehicleInfo.setUseCharacter(useCharacter);
						vehicleInfo.setGoods(goods);
						vehicleInfo.setFileNo(fileNo);
						vehicleInfo.setDrvlicenceType(drvlicenceType);
						vehicleInfo.setCertificateType(certificateType);
						vehicleInfo.setVrdTransportNo(vrdTransportNo);
						vehicleInfo.setVrdTransportExp(vrdTransportExp);
						vehicleInfo.setPolicyExp(policyExp);
						vehicleInfo.setPrdTransportNo(prdTransportNo);
						vehicleInfo.setPrdTransportExp(prdTransportExp);
						vehicleInfo.setSupercargoName(supercargoName);
						vehicleInfo.setCreateTime(createTime);
						
						vehicleInfo.setCheckStatus("0");
						vehicleInfo.setReviewStatus("0");

						dao.save(vehicleInfo);
					}
				}
				
				//再当做承包商车辆信息进行匹配
				params.clear();
				params.put("contractorCode", companyCode);		//当passType=22时这个字段是承包商代码
				params.put("fullName", fullName);
				list = (List<MapBean>) sql.query("dbsync.list-employee-by-contractorcode-fullname", params);
				if (list.size() > 0) {
					String employeeCode = (String) list.get(0).get("employee_code");
					//先判断车辆是否被手工删除过
					querys.clear();
					querys.add(new ORQuery(Op.eq, "employeeCode", employeeCode));
					querys.add(new ORQuery(Op.eq, "plateNo", plateNo));
					
					//查询车辆删除历史信息,如果有删除信息则不再处理关联数据
					List<TVehicleInfoHistory> listVehicleInfoHistory= dao.list(TVehicleInfoHistory.class, querys);
					if (listVehicleInfoHistory.size() == 0) {		//没有做过删除操作时允许再次导入
						TVehicleInfo vehicleInfo = new TVehicleInfo();
						vehicleInfo.setEmployeeCode(employeeCode);
						vehicleInfo.setPlateNo(plateNo);
						vehicleInfo.setPlateColor(plateColor);
						vehicleInfo.setEngineNo(engineNo);
						vehicleInfo.setOwnerName(ownerName);
						vehicleInfo.setVehicleType(vehicleType);
						vehicleInfo.setUseCharacter(useCharacter);
						vehicleInfo.setGoods(goods);
						vehicleInfo.setFileNo(fileNo);
						vehicleInfo.setDrvlicenceType(drvlicenceType);
						vehicleInfo.setCertificateType(certificateType);
						vehicleInfo.setVrdTransportNo(vrdTransportNo);
						vehicleInfo.setVrdTransportExp(vrdTransportExp);
						vehicleInfo.setPolicyExp(policyExp);
						vehicleInfo.setPrdTransportNo(prdTransportNo);
						vehicleInfo.setPrdTransportExp(prdTransportExp);
						vehicleInfo.setSupercargoName(supercargoName);
						vehicleInfo.setCreateTime(createTime);
						
						vehicleInfo.setCheckStatus("0");
						vehicleInfo.setReviewStatus("0");

						dao.save(vehicleInfo);
					}
				}
			}
		}
		logger.debug("Sync list-vehicle-info-1 Information finished.");
		
		
		// t_vehicle_info车辆信息表,根据t_employee_vehicle表取得关联车主关联信息
		logger.debug("Sync update-vehicle-info-rel-employee-vehicle Information begin.");
		params.clear();
		sql.update("dbsync.update-vehicle-info-rel-employee-vehicle", params);
		logger.debug("Sync update-vehicle-info-rel-employee-vehicle Information finished.");
		
		// t_vehicle_info车辆信息表,根据t_contractor_vehicle表取得关联车主关联信息
		logger.debug("Sync update-vehicle-info-rel-contractor-vehicle Information begin.");
		params.clear();
		sql.update("dbsync.update-vehicle-info-rel-contractor-vehicle", params);
		logger.debug("Sync update-vehicle-info-rel-contractor-vehicle Information finished.");
		
		// t_vehicle_info车辆信息表,根据t_vehicle_info表关联t_account_identity表取得车主关联信息
		logger.debug("Sync update-vehicle-info-rel-vehicle-info Information begin.");
		sql.update("dbsync.update-vehicle-info-rel-vehicle-info", params);
		logger.debug("Sync update-vehicle-info-rel-vehicle-info Information finished.");		
		
		//t_review_history复核信息表(人员)
		logger.debug("Sync list-review-history-person Information begin.");
		listData = (List<MapBean>) sql.query("dbsync.list-review-history-person", params);
		for (MapBean d : listData){
			long reivewHistoryId = idService.getCode(TReivewHistory.class.getSimpleName(), 1, true);
			String id = idService.formatCode(10, reivewHistoryId, true, "H");
			
			Timestamp createTime = (Timestamp) d.get("verify_date");
			String createUserid = (String) d.get("verifyer");
			String concernDesc = (String) d.get("focus_desc");
			String concernType = (String) d.get("concern_type");
			String employeeCode = (String) d.get("employee_code");
			String remark = (String) d.get("verify_content");
			String reviewStatus = (String) d.get("review_status");
			String verifyFlag = (String) d.get("verify_flag");
			
			Map<String, Object> values = new HashMap<>();
			values.put("id", id);
			values.put("createTime", createTime);
			values.put("createUserid", createUserid);
			values.put("concernDesc", concernDesc);
			values.put("concernType", concernType);
			values.put("employeeCode", employeeCode);
			values.put("remark", remark);
			values.put("reviewStatus", reviewStatus);
			values.put("reviewType", "0");
			sql.create("dbsync.insert-review-history", values);

			if ("0".equals(verifyFlag) && employeeCode != null) {
				TCompanyEmployee companyEmployee= dao.get(TCompanyEmployee.class, employeeCode);
				companyEmployee.setReviewStatus(reviewStatus);
				companyEmployee.setConcernType(concernType);
				dao.update(companyEmployee);
			}
		}
		logger.debug("Sync list-review-history-person Information finished.");
		
		
		//t_company_employee企业人员信息,更新人员表中的复核标志位
//		logger.debug("Sync update-company-employee-reviewstatus Information begin.");
//		sql.update("dbsync.update-company-employee-reviewstatus", params);
//		logger.debug("Sync update-company-employee-reviewstatus Information finished.");
		
		//t_company_employee企业人员信息,更新人员表中的关注人员字段
//		logger.debug("Sync update-company-employee-concerntype Information begin.");
//		sql.update("dbsync.update-company-employee-concerntype", params);
//		logger.debug("Sync update-company-employee-concerntype Information finished.");
		
		
		logger.debug("Sync data from old db end ");
		

	}
}

